package service;

import com.fasterxml.jackson.databind.ObjectMapper;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/student-gpa-stats")
public class MSTU extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        List<Class_GenderS>   stats=getClass_Gender();

        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(stats);

        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        resp.getWriter().write(json);

    }
    private List<Class_GenderS> getClass_Gender(){
        List<Class_GenderS> stats= new ArrayList<>();
        String url = "jdbc:mysql://192.168.244.11:3306/stats?serverTimezone=Asia/Shanghai&useSSL=false";
        String user = "root";
        String password = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(" SELECT name, gpa FROM student_info  ORDER BY gpa DESC LIMIT 3")) {

            while (rs.next()) {
                stats.add(new Class_GenderS(rs.getString("name"), rs.getDouble("gpa")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }



        return stats;
    }



    private static class Class_GenderS {
        private String name;
        private double gpa;

        public Class_GenderS(String name, double gpa) {
            this.name = name;
            this.gpa = gpa;

        }

        public String getName() { return name; }
        public double getgpa() { return gpa; }

    }
}
